/*
 * To change this template, choose Tools | Templates and open the template in
 * the editor.
 */
package eypweb_bsc;

import java.io.File;
import java.net.URL;
import java.sql.*;
import java.util.ArrayList;
import java.util.ResourceBundle;
import javafx.event.ActionEvent;
import javafx.fxml.FXML;
import javafx.fxml.Initializable;
import javafx.scene.control.*;
import javafx.scene.control.cell.PropertyValueFactory;
import javafx.stage.FileChooser;
import javafx.stage.Stage;

/**
 *
 * @author Kohalmi Istvan
 */
// piaci ertekelest megvalosito kepernyo
// fejlesztes alatt all
public class EvaluationRegistry implements Initializable {
    
    @FXML
    private Button btnPreview;
    @FXML
    private Label errorLabel;
    @FXML
    private Label lCurrency;
    @FXML
    private Label lName;
    @FXML
    private Label lDenomination;
    @FXML
    private Label lDividendHUF;
    @FXML
    private Label lDividendRateDelta;
    @FXML
    private Label lGrossPrice;
    @FXML
    private Label lPriceRateDelta;
    @FXML
    private Label lBase;
    @FXML
    private Label lType;
    @FXML
    private ChoiceBox cbPortfolio;
    @FXML
    private TextField tfAccumulatedInterest;
    @FXML
    private TextField tfAccumulatedInterestPercent;
    @FXML
    private TextField tfAcquisition;
    @FXML
    private TextField tfComment;
    @FXML
    private TextField tfCurrency;
    @FXML
    private TextField tfEvaluationDate;
    @FXML
    private TextField tfEvaluationRate;
    @FXML
    private TextField tfISIN;
    @FXML
    private TextField tfSerial;
    @FXML
    private TextField tfQuantity;
    @FXML
    private TextField tfNominal;
    @FXML
    private TextField tfNetRateCurrency;
    @FXML
    private TextField tfGrossPrice;
    @FXML
    private TableView<Evaluation> tblEvaluation;
    private Isin isin;
    private ArrayList<Integer> portIds;

    @FXML
    private void handleRecordButtonAction(ActionEvent event) {
        
    }

    @FXML
    private void handleCSVButtonAction(ActionEvent event) {
        
    }

    @FXML
    private void handleXLSButtonAction(ActionEvent event) {
        
    }

    @FXML
    private void handleXMLButtonAction(ActionEvent event) {
        
    }

    @FXML
    private void handleISINButtonAction(ActionEvent event) {
        if (tfISIN.getText().isEmpty()) {
            errorLabel.setText("Az ISIN mező kitöltése kötelező!");
            return;
        }
        isin = Isin.getCurrentIsin(tfISIN.getText());
        lName.setText(isin.getName());
        lDenomination.setText(Float.toString(isin.getDenomination()));
        lCurrency.setText(isin.getDeviza().getName());
        lBase.setText(isin.getBase());
        if (lCurrency.getText().equals("HUF")) {
            tfCurrency.setText("1.0");
            tfEvaluationRate.setText("1.0");
            tfNetRateCurrency.setText("1.0");
        }
        int port_id;
        Director director = Director.getInstance();
        AccessInfo ai = director.getAccessInfo();
        tblEvaluation.getItems().clear();
        try {
            Connection con = DriverManager.getConnection("jdbc:sqlserver://" + ai.getIPAddress() + ":" + ai.getPort() + ";databaseName=" + ai.getDBName(), ai.getUser(), ai.getPass());
            PreparedStatement portStmt = con.prepareStatement("SELECT id FROM portfolio WHERE megnev = ? AND ? BETWEEN hatalytol AND hatalyig");
            PreparedStatement inventoryStmt = con.prepareStatement("SELECT * FROM leltar "
                    + "WHERE id_isin = ? "
                    + "AND id_port = ? "
                    + "AND id_vk = ?  "
                    + "AND mennyiseg > 0 "
                    + "AND id IN (SELECT MAX(id) FROM leltar "
                    + "WHERE id_isin = ? "
                    + "AND id_port = ? "
                    + "AND id_vk = ? "
                    + "GROUP BY id_vetel) "
                    + "ORDER BY beszerzes_datum, id_vetel");

            portStmt.setNString(1, cbPortfolio.getSelectionModel().getSelectedItem().toString());
            portStmt.setDate(2, new Date(new java.util.Date().getTime()));
            ResultSet portRS = portStmt.executeQuery();
            portRS.next();
            port_id = portRS.getInt(1);

            inventoryStmt.setInt(1, isin.getId());
            inventoryStmt.setInt(2, port_id);
            inventoryStmt.setInt(3, ai.getTrusteeSign());
            inventoryStmt.setInt(4, isin.getId());
            inventoryStmt.setInt(5, port_id);
            inventoryStmt.setInt(6, ai.getTrusteeSign());
            ResultSet inventoryRS = inventoryStmt.executeQuery();
            while (inventoryRS.next()) {
                Evaluation ev = new Evaluation();
                
                tblEvaluation.getItems().add(ev);
            }
            btnPreview.setDisable(false);
            con.close();
        } catch (SQLException sqle) {
            errorLabel.setText(sqle.getMessage());
        }
    }

    @FXML
    private void handleEscapeButtonAction(ActionEvent event) {
        Director director = Director.getInstance();
        director.initializeMainMenu();
    }
    
    @FXML
    private void handlePreviewButtonAction(ActionEvent event){
        
    }

    @Override
    public void initialize(URL url, ResourceBundle rb) {
        Director director = Director.getInstance();
        AccessInfo ai = director.getAccessInfo();
        
        try {
            Connection con = DriverManager.getConnection("jdbc:sqlserver://" + ai.getIPAddress() + ":" + ai.getPort() + ";databaseName=" + ai.getDBName(), ai.getUser(), ai.getPass());

            String portQuery = "SELECT * FROM portfolio";
            PreparedStatement stmt2 = con.prepareStatement(portQuery);
            ResultSet rs2 = stmt2.executeQuery();
            while (rs2.next()) {
                Integer port_id = new Integer(rs2.getInt(1));
                String port = rs2.getNString(2);
                portIds.add(port_id);
                cbPortfolio.getItems().add(port);
            }
            cbPortfolio.getSelectionModel().select(0);

            con.close();

        } catch (SQLException sqle) {
            errorLabel.setText(sqle.getMessage());
        }
        
        TableColumn<Evaluation, String> col1 = new TableColumn<Evaluation, String>("Elszámolt mennyiség / névérték");
        col1.setCellValueFactory(new PropertyValueFactory("accountedBase"));

        TableColumn<Evaluation, String> col2 = new TableColumn<Evaluation, String>("Vételi ID");
        col2.setCellValueFactory(new PropertyValueFactory("purchaseId"));

        TableColumn<Evaluation, String> col3 = new TableColumn<Evaluation, String>("Beszerzés dátuma");
        col3.setCellValueFactory(new PropertyValueFactory("acquisitionDate"));

        TableColumn<Evaluation, String> col4 = new TableColumn<Evaluation, String>("Mennyiség");
        col4.setCellValueFactory(new PropertyValueFactory("quantity"));

        TableColumn<Evaluation, String> col5 = new TableColumn<Evaluation, String>("Névérték");
        col5.setCellValueFactory(new PropertyValueFactory("nominal"));

        TableColumn<Evaluation, String> col6 = new TableColumn<Evaluation, String>("Beszerzési érték");
        col6.setCellValueFactory(new PropertyValueFactory("acquisitionValue"));

        TableColumn<Evaluation, String> col7 = new TableColumn<Evaluation, String>("Piaci érték");
        col7.setCellValueFactory(new PropertyValueFactory("marketValue"));

        TableColumn<Evaluation, String> col8 = new TableColumn<Evaluation, String>("ÉK - kamat");
        col8.setCellValueFactory(new PropertyValueFactory("derecogniseInterest"));

        TableColumn<Evaluation, String> col9 = new TableColumn<Evaluation, String>("ÉK - osztalék");
        col9.setCellValueFactory(new PropertyValueFactory("derecogniseDividend"));

        TableColumn<Evaluation, String> col10 = new TableColumn<Evaluation, String>("ÉK - egyéb piaci");
        col10.setCellValueFactory(new PropertyValueFactory("derecogniseRateDelta"));

        TableColumn<Evaluation, String> col11 = new TableColumn<Evaluation, String>("ÉK - deviza árfolyam-különbözet");
        col11.setCellValueFactory(new PropertyValueFactory("derecogniseCurrencyRateDelta"));

        TableColumn<Evaluation, String> col12 = new TableColumn<Evaluation, String>("Beszerzéskori árfolyam");
        col12.setCellValueFactory(new PropertyValueFactory("acquisitionRate"));

        TableColumn<Evaluation, String> col13 = new TableColumn<Evaluation, String>("Beszerzéskori deviza árfolyam");
        col13.setCellValueFactory(new PropertyValueFactory("acquisitionRateCurrency"));

        TableColumn<Evaluation, String> col14 = new TableColumn<Evaluation, String>("Kivezetett ÉK kamat");
        col14.setCellValueFactory(new PropertyValueFactory("derecognisedInterest"));

        TableColumn<Evaluation, String> col15 = new TableColumn<Evaluation, String>("Kivezetett ÉK osztalék");
        col15.setCellValueFactory(new PropertyValueFactory("derecognisedDividend"));

        TableColumn<Evaluation, String> col16 = new TableColumn<Evaluation, String>("Kivezetett ÉK egyéb piaci");
        col16.setCellValueFactory(new PropertyValueFactory("derecognisedRateDelta"));

        TableColumn<Evaluation, String> col17 = new TableColumn<Evaluation, String>("Kivezett ÉK deviza árfolyam-különbözet");
        col17.setCellValueFactory(new PropertyValueFactory("derecognisedCurrencyRateDelta"));

        TableColumn<Evaluation, String> col18 = new TableColumn<Evaluation, String>("Számított kamat");
        col18.setCellValueFactory(new PropertyValueFactory("calculatedInterest"));

        TableColumn<Evaluation, String> col19 = new TableColumn<Evaluation, String>("Számított osztalék");
        col19.setCellValueFactory(new PropertyValueFactory("calculatedDividend"));

        TableColumn<Evaluation, String> col20 = new TableColumn<Evaluation, String>("Számított egyéb piaci");
        col20.setCellValueFactory(new PropertyValueFactory("calculatedRateDelta"));

        TableColumn<Evaluation, String> col21 = new TableColumn<Evaluation, String>("Számított deviza árfolyam-különbözet");
        col21.setCellValueFactory(new PropertyValueFactory("calculatedCurrencyRateDelta"));

        TableColumn<Evaluation, String> col22 = new TableColumn<Evaluation, String>("Elszámolandó kamat");
        col22.setCellValueFactory(new PropertyValueFactory("accountableInterest"));
        
        TableColumn<Evaluation, String> col23 = new TableColumn<Evaluation, String>("Elszámolandó osztalék");
        col23.setCellValueFactory(new PropertyValueFactory("accountableDividend"));
        
        TableColumn<Evaluation, String> col24 = new TableColumn<Evaluation, String>("Elszámolandó egyéb piaci");
        col24.setCellValueFactory(new PropertyValueFactory("accountableRateDelta"));
        
        TableColumn<Evaluation, String> col25 = new TableColumn<Evaluation, String>("Elszámolandó deviza árfolyam-különbözet");
        col25.setCellValueFactory(new PropertyValueFactory("accountableInterest"));
        
        tblEvaluation.getColumns().addAll(col1, col2, col3, col4, col5, col6, col7, col8, col9, col10, col11, col12, col13, col14, col15, col16, col17, col18, col19, col20, col21, col22, col23, col24, col25);
    }
}
